/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package statistics;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import base.message.Message;
import routing.RouteNodeAbstraction;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import routing.RouteNodeList;

/**
 *
 * @author Anna
 */
public class DatabaseConnection {

    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DATABASE_URL = "jdbc:mysql://localhost:3306/wnsim";
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;

    public DatabaseConnection() {
        /**
         * connect to database script and query database
         **/
        try {
            //load database driver class
            Class.forName(JDBC_DRIVER);
            //establish connection to database
            connection = DriverManager.getConnection(DATABASE_URL, "root", "anna");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    /**
     * function for entering values/records in database "transaction"
     **/
//    public void recordEntry(Message message) {
//        int sourceId = message.getData().getNodeInfo().getId();
//        int energy = message.getData().getNodeInfo().getEnergy();
//        int coordinateX = message.getData().getNodeInfo().getPosition().getX();
//        int coordinateY = message.getData().getNodeInfo().getPosition().getY();
//        RouteNodeList rnl = message.getTargetNodes();
//		int targetId = rnl.get(0).getNodeId();
//        String messageType = message.getMessageType().toString();
//        int messageSize = message.getMessageSize();
//        int serviceId = message.getData().getServiceId();
//        //Timestamp sqlDate = new Timestamp(new Date().getTime());
//        long time = message.getSendTime();
//		int hopcount = message.getHopCount();
//		String messageId = message.getMessageId();
//
//        try {
//            statement = connection.createStatement();
//            statement.executeUpdate("INSERT INTO transaction " +
//                    "VALUES (0," + sourceId + ", " + targetId +
//                    ",'" + messageType + "'," + messageSize + "," + serviceId + "," + coordinateX + ", " + coordinateY + ","
//					+ energy + "," + time + "," + hopcount + ",'"+ messageId +"')");
//        } catch (SQLException ex) {
//            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
//        }
//    }

    /**
     * function for getting the Energy Progress of a Source Node
     **/
    public ResultSet getEnergyProgress(int source) {

        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT Energy FROM transaction WHERE SourceId=" + source);
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
        return resultSet;
    }

    /**
     * function for getting all distinct types of messages sent by source
     * @param source
     * @return
     */
    public int[] getSourceMessageTypes(int source) {
        int i = 0;
        int k = 0;
        int[] array = null;
        ResultSet resultSet1 = null;

        try {
            statement = connection.createStatement();
            /**
             * Query for retrieving all distinct message types
             **/
            resultSet = statement.executeQuery("SELECT DISTINCT MessageType FROM transaction WHERE SourceId=" + source);
            /**
             *counting the distinct message types
             **/
            while (resultSet.next()) {
                i++;
            }

            /**
             * defining the length of the array
             **/
            array = new int[i];
            /**
             *Query for counting the number of each message type
             **/
            while (resultSet.next()) {
                resultSet1 = statement.executeQuery("SELECT COUNT(MessageType) FROM transaction WHERE SourceId=" + source + "AND MessageType='" + resultSet.getString(1) + "'");
                System.out.println("No of Messages '" + resultSet.getString(1) + "' equals:" + resultSet1.getInt(1));
                array[k] = resultSet1.getInt(1);
                k++;
            }

        /**
         * Storing in array the found numbers of distinct message types
         **/
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
        return array;
    }

    /**
     * function for getting the source route of the source by obtaining the corresponding coordinates
     **/
    public ResultSet getSourceNodeRoute(int source) {

        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT CoordinateX, CoordinateY FROM transaction WHERE SourceId=" + source);
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
        return resultSet;
    }

    /**
     * function for getting the total Message Size of the sent messages by source
     **/
    public int getSourceTotalMessageSize(int source) {
        int total = 0;
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT COUNT(MessageSize) FROM transaction WHERE SourceId=" + source);
            total = resultSet.getInt(1);
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
        return total;
    }

    /**
     * function for getting the total Message Size of the received messages by target
     **/
    public int getTargetTotalMesssageSize(int target) {
        int total = 0;
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT COUNT(MessageSize) FROM transaction WHERE TargetId=" + target);
            total = resultSet.getInt(1);
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
        return total;
    }

    /**
     * function for getting the Message Types received by target Node
     **/
    public ResultSet getTargetMessageTypes(int target) {

        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT MessageType FROM transaction WHERE TargetId=" + target);
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
        return resultSet;
    }

    public void CloseConnections() {
        try {
            resultSet.close();
            statement.close();
            connection.close();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
        }

    }
}



